begin;
-- CREATE SEQUENCE aamaster_seq;
CREATE SEQUENCE _100351.jb_ja_id_seq
    INCREMENT BY 1;
SELECT setval('jb_ja_id_seq',100351);    
CREATE SEQUENCE mc_company_id_seq;
-- SELECT setval('mc_company_id_seq',100351);
CREATE SEQUENCE _100351.mcountry_country_id_seq
    INCREMENT BY 1;
CREATE SEQUENCE _100351.mcurrency_currency_id_seq
    INCREMENT BY 1;
CREATE SEQUENCE _100351.mship_mode_id_seq
    INCREMENT BY 1;
CREATE SEQUENCE ms_state_id_seq;     
CREATE SEQUENCE _100351.mcity_city_id_seq
    INCREMENT BY 1;
CREATE SEQUENCE _100351.mport_port_id_seq INCREMENT BY 1;    
CREATE SEQUENCE _100351.mcompany_contact_contact_id_seq
    INCREMENT BY 1;

--  CREATE TABLE aamaster 
-- 	(
-- 	amid integer DEFAULT nextval('aamaster_seq'::text) NOT NULL ,
-- 	amcode character varying,
-- 	amname dm_description,
-- 	amtype integer,
-- 	amcomment character varying
-- 	);  

-- ALTER TABLE aamaster ADD CONSTRAINT aam_amid_pk primary key(amid);

CREATE TABLE _100351._jbuyer (
    buyer_jid integer DEFAULT nextval('jb_ja_id_seq'::text) NOT
NULL ,
    buyer_code character varying,
    buyer_name character varying,
    "zone" character varying,
    account character varying,
    payment_term character varying,
    shipment_term character varying,
    credit_days character varying,
    agent_name character varying,
    agent_commission numeric,
    port_id integer,
    currency_name character varying
);


CREATE TABLE _100351._jaccount (
    account_jid integer DEFAULT nextval('jb_ja_id_seq'::text) NOT
NULL ,
    account_code character varying,
    account_name character varying,
    "zone" character varying,
    account character varying,
    payment_term character varying,
    shipment_term character varying,
    credit_days character varying,
    agent_name character varying,
    agent_commission numeric,
    currency_name character varying
);

CREATE TABLE _mcountry (
    country_id integer DEFAULT nextval('mcountry_country_id_seq'::text) NOT
NULL ,
    country_code character varying,
    country_name character varying,
    isd_code character varying
);


CREATE TABLE _100351._mcurrency (
    currency_id integer DEFAULT nextval('mcurrency_currency_id_seq'::text)
NOT
NULL ,
    currency_code character varying,
    currency_name character varying
);


CREATE TABLE _100351._mship_mode (
    shipment_mode_id integer DEFAULT nextval('mship_mode_id_seq'::text) NOT
NULL ,
    shipment_mode_code character varying,
    shipment_mode_name character varying
);

CREATE TABLE _100351._mcompany 
	(
	company_id integer DEFAULT nextval('mc_company_id_seq'::text) NOT NULL ,
	company_code character varying,
	company_name character varying
	);

ALTER TABLE _mcompany ADD CONSTRAINT company_id_pk primary key(company_id);

CREATE TABLE _100351._mstate 
	(
	state_id integer DEFAULT nextval('ms_state_id_seq'::text) NOT NULL ,
	state_code character varying,
	state_name character varying
	);

ALTER TABLE _mstate ADD CONSTRAINT state_id_pk primary key(state_id);
		
CREATE TABLE _100351._mcity (
    city_id integer DEFAULT nextval('mcity_city_id_seq'::text) NOT
NULL ,
    city_code character varying,
    city_name character varying,
    state_id character varying,
    country_id character varying,
    area_code character varying
);

CREATE TABLE _100351._mport(
	port_id integer DEFAULT nextval('mport_port_id_seq'::text) NOT
	NULL ,
	port_name character varying,
	port_type_id integer,
	city_id integer
	);

CREATE TABLE _100351.company_contact (
    contact_id integer DEFAULT nextval('mcompany_contact_contact_id_seq'::text) NOT NULL ,
    reference_detail integer,    
    company_id integer,
    address character varying,
    city_name character varying,
    state character varying,
    country_name character varying,
    zipcode character varying,
    phone_number dm_description,
    fax_number dm_description,
    email_address dm_description,
    website dm_description,
    tax_detail dm_description    
);

ALTER TABLE company_contact ADD FOREIGN KEY (company_id) REFERENCES _mcompany(company_id);


-- INSERT INTO aamaster (amcode,amname,amtype) VALUES ('','{Master Name}',2);
-- INSERT INTO aamaster (amcode,amname) VALUES ('','{System}');
-- INSERT INTO aamaster (amcode,amname,amtype) VALUES ('_jtype','{Journal Type}',2);
-- INSERT INTO aamaster (amcode,amname,amtype) VALUES ('_rtype','{Relation Type}',3);
-- INSERT INTO aamaster (amcode,amname) VALUES ('','{}');
-- INSERT INTO aamaster (amcode,amname) VALUES ('','{}');
-- INSERT INTO aamaster (amcode,amname,amtype) VALUES ('_jbuyer','{Buyer}',4);
-- INSERT INTO aamaster (amcode,amname,amtype) VALUES ('_jaccount','{Account}',4);
-- INSERT INTO aamaster (amcode,amname,amtype) VALUES ('_mcountry','{Country}',1);
-- INSERT INTO aamaster (amcode,amname,amtype) VALUES ('_mcity','{City}',1);
-- INSERT INTO aamaster (amcode,amname,amtype) VALUES ('_mport','{Port}',1);
-- INSERT INTO aamaster (amcode,amname,amtype) VALUES ('_mship_mode','{Shipment Mode}',1);
-- INSERT INTO aamaster (amcode,amname,amtype) VALUES ('_mcompany','{Company }',1);
-- INSERT INTO aamaster (amcode,amname,amtype) VALUES ('_mcurrency','{Currency}',1);

CREATE TABLE sys_report_filter (
    report_id integer NOT NULL,
    filter_id integer NOT NULL,
    filter_column integer NOT NULL,
    group_column character varying NOT NULL
);
commit;